package com.codrim.cs.data.server.dao.mapper;

import com.codrim.cs.data.rpc.dto.param.BillCsOfferParam;
import com.codrim.cs.data.rpc.dto.param.StatementOfferDTO;
import com.codrim.cs.data.rpc.dto.param.TopOfferConversionParamDTO;
import com.codrim.cs.data.server.dao.entity.OfferDataDO;
import com.codrim.cs.data.server.dao.entity.StatCsAdvOfferDO;
import com.codrim.cs.data.server.dao.entity.TopOfferConversionDO;
import com.codrim.cs.data.server.dao.entity.unique.key.StatCsAdvOfferUniqueKey;
import com.codrim.cs.data.server.dao.param.OfferDataParam;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * @author Liang.Zhuge on 14/07/2017.
 */
@Mapper
public interface StatCsAdvOfferMapper extends StatCsMapper<StatCsAdvOfferDO, StatCsAdvOfferUniqueKey> {
    String tableName = "e0000_day_stat_cs_adv_offer";
    String generalTableName = "e0000_day_stat_cs_general";

    @Insert("insert into " + tableName + "(" +
            "sdate, " +
            "symbol, " +
            "advertiser_id, " +
            "offer_id, " +
            "click, " +
            "uclick, " +
            "gc, " +
            "conversion, " +
            "aprice, " +
            "pprice, " +
            "caps, " +
            "sc, " +
            "adv_advertiser_manager_id, " +
            "adv_affiliate_manager_id, " +
            "adv_advertiser_manager_department_id, " +
            "adv_affiliate_manager_department_id, " +
            "single_access_price, " +
            "single_put_price " +
            ") values (" +
            "#{param.sdate}, " +
            "#{param.symbol}, " +
            "#{param.advertiserId}, " +
            "#{param.offerId}, " +
            "#{param.click}, " +
            "#{param.uclick}, " +
            "#{param.gc}, " +
            "#{param.conversion}, " +
            "#{param.aprice}, " +
            "#{param.pprice}, " +
            "#{param.caps}, " +
            "#{param.sc}, " +
            "#{param.advAdvertiserManagerId}, " +
            "#{param.advAffiliateManagerId}, " +
            "#{param.advAdvertiserManagerDepartmentId}, " +
            "#{param.advAffiliateManagerDepartmentId}, " +
            "#{param.singleAccessPrice}," +
            "#{param.singlePutPrice}" +
            ")")
    @Override
    void insert(@Param("param") StatCsAdvOfferDO entity,
            @Param("sZone") String sZone,
            @Param("timeDimesion") String timeDimesion);

    @Delete("delete from " + tableName + " where sdate = ${sdate}")
    void deleteSpecifiedDayReportData(@Param("sdate") int sdate, @Param("sZone") String zone);


    @Override
    @Select("<script> select " +
            "id, " +
            "sdate, " +
            "symbol, " +
            "advertiser_id advertiserId, " +
            "offer_id offerId, " +
            "click, " +
            "uclick, " +
            "gc, " +
            "conversion, " +
            "aprice, " +
            "pprice, " +
            "caps, " +
            "sc, " +
            "adv_advertiser_manager_id, " +
            "adv_affiliate_manager_id, " +
            "adv_advertiser_manager_department_id, " +
            "adv_affiliate_manager_department_id, " +
            "single_access_price singleAccessPrice, " +
            "single_put_price singlePutPrice " +
            "from " + tableName +
            " where sdate = #{param.sdate} " +
            "<choose>" +
            "<when test='param.offerId !=null'> and offer_id = #{param.offerId} </when>" +
            "<otherwise> and offer_id is null </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.advertiserId !=null'> and advertiser_id = #{param.advertiserId} </when>" +
            "<otherwise> and advertiser_id is null </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.advAdvertiserManagerId !=null'> and adv_advertiser_manager_id = #{param.advAdvertiserManagerId} </when>" +
            "<otherwise> and adv_advertiser_manager_id is null </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.advAffiliateManagerId !=null'> and adv_affiliate_manager_id = #{param.advAffiliateManagerId} </when>" +
            "<otherwise> and adv_affiliate_manager_id is null </otherwise>" +
            "</choose>" +
            "<choose><when test='param.symbol !=null'> and symbol = #{param.symbol} </when>" +
            "<otherwise> and symbol is null </otherwise></choose>" +
            "<choose><when test='param.singleAccessPrice !=null'> and single_access_price = #{param.singleAccessPrice} </when>" +
            "<otherwise> and single_access_price = 0 </otherwise></choose>" +
            "<choose><when test='param.singlePutPrice !=null'> and single_put_price = #{param.singlePutPrice} </when>" +
            "<otherwise> and single_put_price = 0 </otherwise></choose> " +
            "limit 1 </script>")
    StatCsAdvOfferDO findByKey(@Param("param") StatCsAdvOfferUniqueKey key,
                               @Param("sZone") String zone, @Param("timeDimesion") String timeDimesion);

    /**
     * 查询可对账广告
     * @param param
     * @param zone
     * @return
     */
    @Select("<script> select " +
            "group_concat(distinct(concat(offer_id,'_',sdate))  separator ',' ) offerIdSdates," +
            "group_concat(id separator ',' ) reportIds," +
            "symbol," +
            "sum(click) click, " +
            "offer_id offerId, " +
            "advertiser_id advertiserId," +
            "affiliate_id affiliateId," +
            "customer_id customerId," +
            "sum(conversion) conversion," +
            "single_access_price singleAccessPrice," +
            "single_put_price singlePutPrice ," +
            "sum(gc) gc " +
            "from " + generalTableName +
            "<where>" +
            //查找offerId存在的数据
            "<if test='param.offerIds!=null and param.offerIds.size()>0'>" +
            " offer_id  in <foreach index='index' item='item' collection='param.offerIds' open='(' separator=', ' close=')'>'${item}'</foreach> " +
            "</if>" +
            //对账公司id
            "<if test='param.advertiserId!=null'>" +
            "and advertiser_id = #{param.advertiserId} " +
            "</if>" +
            //渠道id
            "<if test='param.affiliateId!=null'>" +
            "and affiliate_id = #{param.affiliateId} " +
            "</if>" +
            //广告主id
            "<if test='param.customerId!=null'>" +
            "and customer_id = #{param.customerId}" +
            "</if>" +
            "<if test='param.startTime!=null and param.endTime!=null'>" +
            "and sdate between #{param.startTime} and #{param.endTime}" +
            "</if>" +
            //过滤已经对账过的数据
            "<if test='param.offerIdSdateList !=null  and param.offerIdSdateList.size()>0 ' >" +
            "and concat(offer_id,'_',sdate) not in <foreach index='index' item='item' collection='param.offerIdSdateList' open='(' separator=', ' close=')'>'${item}'</foreach> " +
            "</if>" +
            "</where>" +

            "<choose>" +
            //如果是下载渠道分配数的就传这个
            "<when test='param.isDownLoad!=null and param.isDownLoad ' >" +
            "group by symbol,offer_id,affiliate_id ,single_put_price,DATE_FORMAT(sdate,'%Y-%m') " +
            "order by symbol,offer_id,affiliate_id asc " +
            "</when>" +
            "<otherwise>" +
            "<if test='param.billType==1 ' >" +
            //有转化数的才对账
            "and  gc>0 " +
            "group by symbol,offer_id,single_access_price,DATE_FORMAT(sdate,'%Y-%m') " +
            "order by symbol,offer_id,single_access_price asc " +
            "</if>" +
            "<if test='param.billType==2 ' >" +
            //有转化数的才对账
            "and  conversion>0 " +
            "group by symbol,offer_id,single_access_price,single_put_price,DATE_FORMAT(sdate,'%Y-%m') " +
            "order by symbol,offer_id,single_access_price,single_put_price asc " +
            "</if>" +
            "</otherwise>" +
            "</choose>" +

            "limit #{param.start},#{param.end}" +
            "</script>")
    List<StatementOfferDTO> findAdvOfferByParam(@Param("param") Map param,
                                                @Param("sZone") String zone);

    @Select("<script>select count(distinct(concat(offer_id,'_',sdate))) from " + generalTableName +
            "<where>" +
            //查找offerId存在的数据
            "<if test='param.offerIds!=null and param.offerIds.size()>0'>" +
            " offer_id  in <foreach index='index' item='item' collection='param.offerIds' open='(' separator=', ' close=')'>'${item}'</foreach> " +
            "</if>" +
            //对账公司id
            "<if test='param.advertiserId!=null'>" +
            "and advertiser_id = #{param.advertiserId} " +
            "</if>" +
            //渠道id
            "<if test='param.affiliateId!=null'>" +
            "and affiliate_id = #{param.affiliateId} " +
            "</if>" +
            //广告主id
            "<if test='param.customerId!=null'>" +
            "and customer_id = #{param.customerId}" +
            "</if>" +
            "<if test='param.billType==1 ' >" +
            //有转化数的才对账
            "and gc >0 " +
            "</if>" +
            "<if test='param.billType==2 ' >" +
            //有转化数的才对账
            "and conversion >0 " +
            "</if>" +
            "<if test='param.startTime!=null and param.endTime!=null'>" +
            "and sdate between #{param.startTime} and #{param.endTime}" +
            "</if>" +
            //过滤已经对账过的数据
            "<if test='param.offerIdSdateList !=null  and param.offerIdSdateList.size()>0 ' >" +
            "and concat(offer_id,'_',sdate) not in <foreach index='index' item='item' collection='param.offerIdSdateList' open='(' separator=', ' close=')'>'${item}'</foreach> " +
            "</if>" +
            "</where>" +
            "</script>")
    Integer findAdvOfferByParamCount(@Param("param") Map param,
                                     @Param("sZone") String zone);

    /**
     * 获取手动录入的广告
     * @param param
     * @return
     */
    @Select("<script> select " +
            "group_concat(distinct(concat(offer_id,'_',DATE_FORMAT(sdate,'%Y%m%d')))  separator ',' ) offerIdSdates," +
            "group_concat(id separator ',' ) reportIds, "+
            "symbol," +
            "sum(clicks) click, " +
            "offer_id offerId, " +
            "advertiser_id advertiserId," +
            "affiliate_id affiliateId," +
            "customer_id customerId," +
            "sum(conversions) conversion, " +
            "sum(gross_conversions) gc "+
            "from tb_data_record "+
            "<where>" +
            //查找offerId存在的数据
            "<if test='param.offerIds!=null and param.offerIds.size()>0'>" +
            " offer_id  in <foreach index='index' item='item' collection='param.offerIds' open='(' separator=', ' close=')'>'${item}'</foreach> " +
            "</if>" +
            //对账公司id
            "<if test='param.advertiserId!=null'>" +
            "and advertiser_id = #{param.advertiserId} " +
            "</if>" +
            //渠道id
            "<if test='param.affiliateId!=null'>" +
            "and affiliate_id = #{param.affiliateId} " +
            "</if>" +
            //广告主id
            "<if test='param.customerId!=null'>" +
            "and customer_id = #{param.customerId}" +
            "</if>" +
            "<if test='param.startTime!=null and param.endTime!=null'>" +
            "and sdate between #{param.startTime} and #{param.endTime}" +
            "</if>" +
            //过滤已经对账过的数据
            "<if test='param.offerIdSdateList !=null  and param.offerIdSdateList.size()>0 ' >" +
            "and concat(offer_id,'_',DATE_FORMAT(sdate,'%Y%m%d')) not in <foreach index='index' item='item' collection='param.offerIdSdateList' open='(' separator=', ' close=')'>'${item}'</foreach> " +
            "</if>" +
            "</where>" +

            "<choose>" +
            //如果是下载渠道分配数的就传这个
            "<when test='param.isDownLoad!=null and param.isDownLoad ' >" +
            "group by symbol,offer_id,affiliate_id ,DATE_FORMAT(sdate,'%Y-%m') " +
            "order by symbol,offer_id,affiliate_id asc " +
            "</when>" +
            "<otherwise>" +
            "<if test='param.billType==1 ' >" +
            //有转化数的才对账
            "and gross_conversions >0 " +
            "group by symbol,offer_id,DATE_FORMAT(sdate,'%Y-%m') " +
            "order by symbol,offer_id asc " +
            "</if>" +
            "<if test='param.billType==2 ' >" +
            //有转化数的才对账
            "and conversions >0 " +
            "group by symbol,offer_id,DATE_FORMAT(sdate,'%Y-%m') " +
            "order by symbol,offer_id asc " +
            "</if>" +
            "</otherwise>" +
            "</choose>" +

            "limit #{param.start},#{param.end}" +
            "</script>")
    List<StatementOfferDTO> findAdvOfferManualByParam(@Param("param") Map param);

    /**
     * 统计不对接广告总数
     */
    @Select("<script>select count(distinct(concat(offer_id,'_',DATE_FORMAT(sdate,'%Y%m%d')))) from tb_data_record "  +
            "<where>" +
            //查找offerId存在的数据
            "<if test='param.offerIds!=null and param.offerIds.size()>0'>" +
            "offer_id  in <foreach index='index' item='item' collection='param.offerIds' open='(' separator=', ' close=')'>'${item}'</foreach> " +
            "</if>" +
            "<if test='param.advertiserId!=null'>" +
            "and advertiser_id = #{param.advertiserId} " +
            "</if>" +
            "<if test='param.affiliateId!=null'>" +
            "and affiliate_id = #{param.affiliateId} " +
            "</if>" +
            "<if test='param.customerId!=null'>" +
            "and customer_id = #{param.customerId} " +
            "</if>" +
            "and conversions >0 " +
            "<if test='param.startTime!=null and param.endTime!=null'>" +
            "and sdate between #{param.startTime} and #{param.endTime} " +
            "</if>" +
            //过滤已经对账过的数据
            "<if test='param.offerIdSdateList !=null  and param.offerIdSdateList.size()>0 ' >" +
            "and concat(offer_id,'_',DATE_FORMAT(sdate,'%Y%m%d')) not in <foreach index='index' item='item' collection='param.offerIdSdateList' open='(' separator=', ' close=')'>'${item}'</foreach> " +
            "</if>" +
            "</where>" +
            "</script>")
    Integer findAdvOfferManualByParamCount(@Param("param") Map param);



    @Update("update " + tableName + " set " +
            "symbol = #{value.symbol}, " +
            "click = #{value.click}, " +
            "uclick = #{value.uclick}, " +
            "gc = #{value.gc}, " +
            "conversion = #{value.conversion}, " +
            "aprice = #{value.aprice}, " +
            "pprice = #{value.pprice}, " +
            "caps = #{value.caps}, " +
            "sc = #{value.sc} " +
            " where id = #{id}")
    @Override
    void updateById(@Param("value") StatCsAdvOfferDO dayStatCsAdvOfferDO,
                    @Param("id") Integer id,
                    @Param("sZone") String zone, @Param("timeDimesion") String timeDimesion);

    @Select("<script>" +
            "select " +
            "offer_id offerId," +
            "symbol," +
            "sum(click) clicks," +
            "sum(uclick) uniqueClicks," +
            "sum(gc) grossConversions," +
            "sum(conversion) conversions," +
            "sum(sc) scrubConversions," +
            "sum(aprice) revenue," +
            "sum(pprice) cost " +
            "from " + tableName +
            " where sdate between #{param.startDay} and #{param.endDay} " +
            "and advertiser_id = #{param.companyId} " +
            "<if test='param.offerIds != null and param.offerIds.size()!=0'>" +
            "and offer_id in " +
            "<foreach item='item' index='index' collection='param.offerIds' open='(' separator=', ' close=') '>" +
            "${item}" +
            "</foreach>" +
            "</if>" +
            "group by offer_id, symbol" +
            "</script>")
    List<OfferDataDO> offerDatas(@Param("param") OfferDataParam param, @Param("sZone") String zone);


    @Select("<script> select offer_id offerId, sum(conversion) conversion " +
            "from " + tableName +
            " where advertiser_id = #{param.advertiserId} and sdate between #{begin} and #{end} "+
            "<if test='param.hasAllPermission==false'>"+
            "  and (1>2 "+
                "<if test='param.advertiserManagerId != null'>"+
                "   or adv_advertiser_manager_id = #{param.advertiserManagerId} "+
                "</if>"+
                "<if test='param.affiliateManagerId != null'>"+
                "   or adv_affiliate_manager_id = #{param.affiliateManagerId} "+
                "</if>"+
                "<if test='param.advertiserManagerDepartmentIdSet != null and param.advertiserManagerDepartmentIdSet.size()!=0' >" +
                "    or adv_advertiser_manager_department_id in " +
                "    <foreach item='item' index='index' collection='param.advertiserManagerDepartmentIdSet' open='(' separator=',' close=')'>" +
                "       #{item}" +
                "    </foreach>" +
                "</if>" +
                "<if test='param.affiliateManagerDepartmentIdSet != null and param.affiliateManagerDepartmentIdSet.size()!=0' >" +
                "    or adv_affiliate_manager_department_id in " +
                "    <foreach item='item' index='index' collection='param.affiliateManagerDepartmentIdSet' open='(' separator=',' close=')'>" +
                "       #{item}" +
                "    </foreach>" +
                "</if>" +
                " )" +
            "</if> "+
            "group by offer_id order by sum(conversion) desc limit #{param.num} </script>")
    List<TopOfferConversionDO> topOfferConversions(@Param("param")TopOfferConversionParamDTO param,
            @Param("begin") Integer begin, @Param("end") Integer end, @Param("sZone") String zone);


    @Select("<script>" +
            "SELECT" +
            "  date_format(sdate, '%Y%m') AS sdate," +
            "  symbol                      AS symbol," +
            "  sum(clicks)                   AS click," +
            "  offer_id                    AS offerId," +
            "  advertiser_id               AS advertiserId," +
            "  affiliate_id                AS affiliateId," +
            "  customer_id                 AS customerId," +
            "  adv_affiliate_manager_id   AS advAdvertiserManagerId," +
            "  adv_advertiser_manager_id   AS advAffiliateManagerId," +
            "  aff_advertiser_manager_id   AS affAdvertiserManagerId," +
            "  sum(conversions)            AS conversion," +
            "  sum(gross_conversions)      AS gc " +
            "FROM tb_data_record " +
            "WHERE  conversions >=0  " +
            //对账公司id
            "<if test='param.advertiserId!=null'>" +
            " and advertiser_id = #{param.advertiserId} " +
            "</if>" +
            //渠道id
            "<if test='param.affiliateId!=null'>" +
            " and affiliate_id = #{param.affiliateId} " +
            "</if>" +
            //广告主id
            "<if test='param.customerId!=null'>" +
            " and customer_id = #{param.customerId} " +
            "</if>" +
            //有转化数的才对账
            "<if test='param.startTime!=null and param.endTime!=null'>" +
            " and sdate between #{param.startTime} and #{param.endTime} " +
            "</if>" +
            " GROUP BY symbol,offer_id,affiliate_id,sdate " +
            " ORDER BY sdate DESC " +
            "limit #{param.page},#{param.rows}" +
            "</script>")
    List<StatementOfferDTO> findAdvOfferManualByMapParam(@Param("param") BillCsOfferParam param);
}
